USE [SaleManagement]
GO

-- Drop the dbo.tblCustomer_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblCustomer_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblCustomer_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblCustomer table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblCustomer_Insert
(
	@Id int    OUTPUT,
	@CustomerNo varchar (32)  ,
	@Name nvarchar (128)  ,
	@Address nvarchar (256)  ,
	@Phone varchar (32)  ,
	@Fax varchar (32)  ,
	@Email varchar (32)  
)
AS

		DECLARE @MaxId INT
		DECLARE @Code VARCHAR(10)
		DECLARE @Temp varchar(6)

		SELECT @MaxId = ISNULL(Max(Id),0) from tblCustomer

		 
	  SET @Temp = @MaxId + 1
	  SET @Code = REPLICATE('0', 6 - LEN(@Temp)) + @Temp

	  SELECT @Code = 'CUS-' + @Code

		INSERT INTO [dbo].[tblCustomer]
			(
			[CustomerNo]
			,[Name]
			,[Address]
			,[Phone]
			,[Fax]
			,[Email]
			)
		VALUES
			(
			@Code
			,@Name
			,@Address
			,@Phone
			,@Fax
			,@Email
			)
		
		-- Get the identity value
		SET @Id = SCOPE_IDENTITY()
								

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

-- Drop the dbo.tblImport_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblImport_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblImport_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblImport table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblImport_Insert
(

	@Id int    OUTPUT,

	@RepositoryId int   ,

	@RefNo varchar (16)  ,

	@ImportDate datetime   ,

	@TotalAmount int   ,

	@Notes nvarchar (1024)  ,

	@UpdatedDate datetime   ,

	@UpdatedBy varchar (50)  
)
AS

		DECLARE @MaxId INT
		DECLARE @Code VARCHAR(10)
		DECLARE @Temp varchar(6)

		SELECT @MaxId = ISNULL(Max(Id),0) from tblImport
		 
	  SET @Temp = @MaxId + 1
	  SET @Code = REPLICATE('0', 6 - LEN(@Temp)) + @Temp

	  SELECT @Code = 'IMP-' + @Code
				
		INSERT INTO [dbo].[tblImport]
			(
			[RepositoryId]
			,[RefNo]
			,[ImportDate]
			,[TotalAmount]
			,[Notes]
			,[UpdatedDate]
			,[UpdatedBy]
			)
		VALUES
			(
			@RepositoryId
			,@Code
			,@ImportDate
			,@TotalAmount
			,@Notes
			,@UpdatedDate
			,@UpdatedBy
			)
		
		-- Get the identity value
		SET @Id = SCOPE_IDENTITY()

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

-- Drop the dbo.tblAccessoryImport_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblAccessoryImport_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblAccessoryImport_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblAccessoryImport table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblAccessoryImport_Insert
(

	@Id int    OUTPUT,

	@RefNo varchar (16)  ,

	@ImportDate datetime   ,

	@TotalAmount int   ,

	@Notes nvarchar (1024)  ,

	@UpdatedBy varchar (50)  ,

	@UpdatedDate datetime   
)
AS

		DECLARE @MaxId INT
		DECLARE @Code VARCHAR(10)
		DECLARE @Temp varchar(6)

		SELECT @MaxId = ISNULL(Max(Id),0) from tblAccessoryImport
		 
	  SET @Temp = @MaxId + 1
	  SET @Code = REPLICATE('0', 6 - LEN(@Temp)) + @Temp

	  SELECT @Code = 'ACC-' + @Code

		INSERT INTO [dbo].[tblAccessoryImport]
			(
			[RefNo]
			,[ImportDate]
			,[TotalAmount]
			,[Notes]
			,[UpdatedBy]
			,[UpdatedDate]
			)
		VALUES
			(
			@Code
			,@ImportDate
			,@TotalAmount
			,@Notes
			,@UpdatedBy
			,@UpdatedDate
			)
		
		-- Get the identity value
		SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

-- Drop the dbo.tblExport_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExport_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExport_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblExport table
----------------------------------------------------------------------------------------------------
*/

CREATE PROCEDURE dbo.tblExport_Insert
(
	@Id int    OUTPUT,

	@RefNo varchar (16)  ,

	@FromRepositoryId int   ,

	@ToRepositoryId int   ,

	@ExportDate datetime   ,

	@TotalAmount int   ,

	@Notes nvarchar (1024)  ,

	@UpdatedDate datetime   ,

	@UpdatedBy varchar (50)  
)
AS
	DECLARE @MaxId INT
	DECLARE @Code VARCHAR(10)
	DECLARE @Temp varchar(6)

	SELECT @MaxId = ISNULL(Max(Id),0) from tblExport
	 
  SET @Temp = @MaxId + 1
  SET @Code = REPLICATE('0', 6 - LEN(@Temp)) + @Temp

  SELECT @Code = 'EXP-' + @Code

	INSERT INTO [dbo].[tblExport]
		(
		[RefNo]
		,[FromRepositoryId]
		,[ToRepositoryId]
		,[ExportDate]
		,[TotalAmount]
		,[Notes]
		,[UpdatedDate]
		,[UpdatedBy]
		)
	VALUES
		(
		@Code
		,@FromRepositoryId
		,@ToRepositoryId
		,@ExportDate
		,@TotalAmount
		,@Notes
		,@UpdatedDate
		,@UpdatedBy
		)
	
	-- Get the identity value
	SET @Id = SCOPE_IDENTITY()
GO

-- Drop the dbo.tblPurchase_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchase_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchase_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblPurchase table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPurchase_Insert
(

	@Id int    OUTPUT,

	@RepositoryId int   ,

	@RefNo varchar (16)  ,

	@CustomerId int   ,

	@Date datetime   ,

	@Total int   ,

	@Commission int   ,

	@RealTotal int   ,

	@Notes nvarchar (1024)  ,

	@CreatedDate datetime   ,

	@CreatedBy varchar (50)  ,

	@UpdatedDate datetime   ,

	@UpdatedBy varchar (50)  ,

	@RequiredModified ntext   ,

	@Status char (1) 
)
AS

		DECLARE @MaxId INT
		DECLARE @Code VARCHAR(10)
		DECLARE @Temp varchar(6)

		SELECT @MaxId = ISNULL(Max(Id),0) from tblPurchase
		 
	  SET @Temp = @MaxId + 1
	  SET @Code = REPLICATE('0', 6 - LEN(@Temp)) + @Temp

	  SELECT @Code = 'PUR-' + @Code
				
		INSERT INTO [dbo].[tblPurchase]
		(
		[RepositoryId]
		,[RefNo]
		,[CustomerId]
		,[Date]
		,[Total]
		,[Commission]
		,[RealTotal]
		,[Notes]
		,[CreatedDate]
		,[CreatedBy]
		,[UpdatedDate]
		,[UpdatedBy]
		,[RequiredModified]
		,[Status]
		)
	VALUES
		(
		@RepositoryId
		,@Code
		,@CustomerId
		,@Date
		,@Total
		,@Commission
		,@RealTotal
		,@Notes
		,@CreatedDate
		,@CreatedBy
		,@UpdatedDate
		,@UpdatedBy
		,@RequiredModified
		,@Status
		)
	
	-- Get the identity value
	SET @Id = SCOPE_IDENTITY()
	SET IDENTITY_INSERT tblBackupPurchase ON

		INSERT INTO tblBackupPurchase ([Id],[RepositoryId],[RefNo],[CustomerId],[Date],[Total],[Commission],[RealTotal],[Notes])
				SELECT Id,[RepositoryId],[RefNo],[CustomerId],[Date],[Total],[Commission],[RealTotal],[Notes] from tblPurchase WHERE Id = @Id

	SET IDENTITY_INSERT tblBackupPurchase OFF
			
GO

-- Drop the dbo.tblImportDetail_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblImportDetail_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblImportDetail_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblImportDetail table
----------------------------------------------------------------------------------------------------
*/

CREATE PROCEDURE dbo.tblImportDetail_Insert
(

	@Id int    OUTPUT,

	@ImportId int   ,

	@ProductId int   ,

	@Quantity int   
)
AS
BEGIN TRAN
	-- Insert ImportDetail
	INSERT INTO [dbo].[tblImportDetail]
		(
		[ImportId]
		,[ProductId]
		,[Quantity]
		)
	VALUES
		(
		@ImportId
		,@ProductId
		,@Quantity
		)	
	
	IF(@@ERROR<>0)
		GOTO HANDLEERR

	-- Get the identity value
	SET @Id = SCOPE_IDENTITY()

	IF(@@ERROR<>0)
		GOTO HANDLEERR

	-- Update StoredProduct
	DECLARE @RepositoryId INT
	SELECT @RepositoryId = [RepositoryId]
	FROM tblImport
	WHERE [Id] = @ImportId

	IF EXISTS (SELECT *
						 FROM tblStoredProduct
						 WHERE [RepositoryId] = @RepositoryId AND [ProductId] = @ProductId)
		BEGIN
			UPDATE tblStoredProduct
			SET [CurrentQuantity] = [CurrentQuantity] + @Quantity
			WHERE [RepositoryId] = @RepositoryId AND [ProductId] = @ProductId
		END
	ELSE
		BEGIN
			INSERT INTO tblStoredProduct ([RepositoryId], [ProductId], [CurrentQuantity])
			VALUES (@RepositoryId, @ProductId, @Quantity)
		END

	IF(@@ERROR<>0)
	GOTO HANDLEERR

COMMIT TRAN
RETURN
HANDLEERR:
	ROLLBACK TRAN
GO

-- Drop the dbo.tblImportDetail_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblImportDetail_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblImportDetail_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblImportDetail table
----------------------------------------------------------------------------------------------------
*/

CREATE PROCEDURE dbo.tblImportDetail_Update
(

	@Id int   ,

	@ImportId int   ,

	@ProductId int   ,

	@Quantity int   
)
AS
BEGIN TRAN
	-- Get current row
	DECLARE @OldProductId INT
	DECLARE @OldQuantity INT

	SELECT @OldProductId = [ProductId], @OldQuantity = [Quantity]
	FROM tblImportDetail
	WHERE [Id] = @Id

	-- Modify the updatable columns
	UPDATE
		[dbo].[tblImportDetail]
	SET
		[ImportId] = @ImportId
		,[ProductId] = @ProductId
		,[Quantity] = @Quantity
	WHERE
		[Id] = @Id
	
	IF(@@ERROR<>0)
		GOTO HANDLEERR

	-- Update StoredProduct
	DECLARE @RepositoryId INT
	SELECT @RepositoryId = [RepositoryId]
	FROM tblImport
	WHERE [Id] = @ImportId

	UPDATE tblStoredProduct
	SET [CurrentQuantity] = [CurrentQuantity] - @OldQuantity
	WHERE [RepositoryId] = @RepositoryId AND [ProductId] = @OldProductId

	IF(@@ERROR<>0)
	GOTO HANDLEERR
	
	UPDATE tblStoredProduct
	SET [CurrentQuantity] = [CurrentQuantity] + @Quantity
	WHERE [RepositoryId] = @RepositoryId AND [ProductId] = @ProductId

	IF(@@ERROR<>0)
	GOTO HANDLEERR

COMMIT TRAN
RETURN
HANDLEERR:
	ROLLBACK TRAN
GO

-- Drop the dbo.tblImportDetail_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblImportDetail_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblImportDetail_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblImportDetail table
----------------------------------------------------------------------------------------------------
*/

CREATE PROCEDURE dbo.tblImportDetail_Delete
(
	@Id int   
)
AS
BEGIN TRAN
	-- Get current row
	DECLARE @Quantity INT
	DECLARE @ProductId INT
	DECLARE @ImportId INT
	SELECT @ImportId = [ImportId], @ProductId = [ProductId], @Quantity = [Quantity]
	FROM tblImportDetail
	WHERE [Id] = @Id
	
	-- Delete ImportDetail
	DELETE FROM [dbo].[tblImportDetail] WITH (ROWLOCK) 
	WHERE
		[Id] = @Id	
	
	IF(@@ERROR<>0)
		GOTO HANDLEERR
	
	-- Update StoredProduct
	DECLARE @RepositoryId INT
	SELECT @RepositoryId = [RepositoryId]
	FROM tblImport
	WHERE [Id] = @ImportId

	UPDATE tblStoredProduct
	SET [CurrentQuantity] = [CurrentQuantity] - @Quantity
	WHERE [RepositoryId] = @RepositoryId AND [ProductId] = @ProductId

	IF(@@ERROR<>0)
	GOTO HANDLEERR

COMMIT TRAN
RETURN
HANDLEERR:
	ROLLBACK TRAN
GO

-- Drop the dbo.tblExportDetail_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExportDetail_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExportDetail_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblExportDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblExportDetail_Insert
(

	@Id int    OUTPUT,

	@ExportId int   ,

	@ProductId int   ,

	@Quantity int   
)
AS
BEGIN TRAN
	-- Insert ExportDetail
	INSERT INTO [dbo].[tblExportDetail]
		(
		[ExportId]
		,[ProductId]
		,[Quantity]
		)
	VALUES
		(
		@ExportId
		,@ProductId
		,@Quantity
		)	
	
	IF(@@ERROR<>0)
		GOTO HANDLEERR

	-- Get the identity value
	SET @Id = SCOPE_IDENTITY()

	IF(@@ERROR<>0)
		GOTO HANDLEERR

	-- Update StoredProduct
	DECLARE @FromRepositoryId INT
	DECLARE @ToRepositoryId INT
	SELECT @FromRepositoryId = [FromRepositoryId], @ToRepositoryId = [ToRepositoryId]
	FROM tblExport
	WHERE [Id] = @ExportId

	UPDATE tblStoredProduct
	SET [CurrentQuantity] = [CurrentQuantity] - @Quantity
	WHERE [RepositoryId] = @FromRepositoryId AND [ProductId] = @ProductId

	IF(@@ERROR<>0)
	GOTO HANDLEERR

	IF EXISTS (SELECT *
						 FROM tblStoredProduct
						 WHERE [RepositoryId] = @ToRepositoryId AND [ProductId] = @ProductId)
		BEGIN
			UPDATE tblStoredProduct
			SET [CurrentQuantity] = [CurrentQuantity] + @Quantity
			WHERE [RepositoryId] = @ToRepositoryId AND [ProductId] = @ProductId
		END
	ELSE
		BEGIN
			INSERT INTO tblStoredProduct ([RepositoryId], [ProductId], [CurrentQuantity])
			VALUES (@ToRepositoryId, @ProductId, @Quantity)
		END

	IF(@@ERROR<>0)
	GOTO HANDLEERR

COMMIT TRAN
RETURN
HANDLEERR:
	ROLLBACK TRAN
GO

-- Drop the dbo.tblExportDetail_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExportDetail_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExportDetail_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblExportDetail table
----------------------------------------------------------------------------------------------------
*/

CREATE PROCEDURE dbo.tblExportDetail_Update
(

	@Id int   ,

	@ExportId int   ,

	@ProductId int   ,

	@Quantity int  
)
AS
BEGIN TRAN
	-- Get current row
	DECLARE @OldProductId INT
	DECLARE @OldQuantity INT

	SELECT @OldProductId = [ProductId], @OldQuantity = [Quantity]
	FROM tblExportDetail
	WHERE [Id] = @Id

	-- Modify the updatable columns
	UPDATE
		[dbo].[tblExportDetail]
	SET
		[ExportId] = @ExportId
		,[ProductId] = @ProductId
		,[Quantity] = @Quantity
	WHERE
		[Id] = @Id
	
	IF(@@ERROR<>0)
		GOTO HANDLEERR

	-- Update StoredProduct
	DECLARE @FromRepositoryId INT
	DECLARE @ToRepositoryId INT
	SELECT @FromRepositoryId = [FromRepositoryId], @ToRepositoryId = [ToRepositoryId]
	FROM tblExport
	WHERE [Id] = @ExportId
	
	-- Update with old product
	UPDATE tblStoredProduct
	SET [CurrentQuantity] = [CurrentQuantity] + @OldQuantity
	WHERE [RepositoryId] = @FromRepositoryId AND [ProductId] = @OldProductId

	IF(@@ERROR<>0)
	GOTO HANDLEERR
	
	UPDATE tblStoredProduct
	SET [CurrentQuantity] = [CurrentQuantity] - @OldQuantity
	WHERE [RepositoryId] = @ToRepositoryId AND [ProductId] = @OldProductId
	
	IF(@@ERROR<>0)
	GOTO HANDLEERR

	-- Update with product
	UPDATE tblStoredProduct
	SET [CurrentQuantity] = [CurrentQuantity] - @Quantity
	WHERE [RepositoryId] = @FromRepositoryId AND [ProductId] = @ProductId

	IF(@@ERROR<>0)
	GOTO HANDLEERR
	
	UPDATE tblStoredProduct
	SET [CurrentQuantity] = [CurrentQuantity] + @Quantity
	WHERE [RepositoryId] = @ToRepositoryId AND [ProductId] = @ProductId
	
	IF(@@ERROR<>0)
	GOTO HANDLEERR

COMMIT TRAN
RETURN
HANDLEERR:
	ROLLBACK TRAN 
GO

-- Drop the dbo.tblExportDetail_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExportDetail_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExportDetail_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblExportDetail table
----------------------------------------------------------------------------------------------------
*/

CREATE PROCEDURE dbo.tblExportDetail_Delete
(
	@Id int   
)
AS
BEGIN TRAN
	-- Get current row
	DECLARE @Quantity INT
	DECLARE @ProductId INT
	DECLARE @ExportId INT
	SELECT @ExportId = [ExportId], @ProductId = [ProductId], @Quantity = [Quantity]
	FROM tblExportDetail
	WHERE [Id] = @Id
	
	-- Delete ImportDetail
	DELETE FROM [dbo].[tblExportDetail] WITH (ROWLOCK) 
	WHERE
		[Id] = @Id	
	
	IF(@@ERROR<>0)
		GOTO HANDLEERR
	
	-- Update StoredProduct
	DECLARE @FromRepositoryId INT
	DECLARE @ToRepositoryId INT
	SELECT @FromRepositoryId = [FromRepositoryId], @ToRepositoryId = [ToRepositoryId]
	FROM tblExport
	WHERE [Id] = @ExportId

	UPDATE tblStoredProduct
	SET [CurrentQuantity] = [CurrentQuantity] + @Quantity
	WHERE [RepositoryId] = @FromRepositoryId AND [ProductId] = @ProductId

	IF(@@ERROR<>0)
	GOTO HANDLEERR

	UPDATE tblStoredProduct
	SET [CurrentQuantity] = [CurrentQuantity] - @Quantity
	WHERE [RepositoryId] = @ToRepositoryId AND [ProductId] = @ProductId

	IF(@@ERROR<>0)
	GOTO HANDLEERR

COMMIT TRAN
RETURN
HANDLEERR:
	ROLLBACK TRAN
GO

-- Drop the dbo.tblPurchaseDetail_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchaseDetail_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchaseDetail_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblPurchaseDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[tblPurchaseDetail_Insert]
(

	@Id int    OUTPUT,

	@Code varchar (16)  ,

	@PurchaseId int   ,

	@ProductId int   ,

	@Quantity float   ,

	@Price int   
)
AS
BEGIN TRAN
	-- Insert PurchaseDetail
	INSERT INTO [dbo].[tblPurchaseDetail]
		(
		[Code]
		,[PurchaseId]
		,[ProductId]
		,[Quantity]
		,[Price]
		)
	VALUES
		(
		 @Code
		,@PurchaseId
		,@ProductId
		,@Quantity
		,@Price
		)
	
	IF(@@ERROR<>0)
		GOTO HANDLEERR

	-- Get the identity value
	SET @Id = SCOPE_IDENTITY() 
	
	IF(@@ERROR<>0)
		GOTO HANDLEERR

	SET IDENTITY_INSERT tblBackupPurchaseDetail ON

		INSERT INTO tblBackupPurchaseDetail([Id],[Code],[PurchaseId],[ProductId],[Quantity],[Price])
					SELECT * FROM tblPurchaseDetail WHERE Id = @Id

	SET IDENTITY_INSERT tblBackupPurchaseDetail OFF
	
	IF(@@ERROR<>0)
		GOTO HANDLEERR
	
	-- Update to Store
	DECLARE @RepositoryId INT
	SELECT @RepositoryId = [RepositoryId]
	FROM tblPurchase
	WHERE [Id] = @PurchaseId

	UPDATE tblStoredProduct
	SET [CurrentQuantity] = [CurrentQuantity] - @Quantity
	WHERE [RepositoryId] = @RepositoryId AND [ProductId] = @ProductId

	IF(@@ERROR<>0)
	GOTO HANDLEERR

COMMIT TRAN
RETURN
HANDLEERR:
	ROLLBACK TRAN
GO

-- Drop the dbo.tblPurchaseDetail_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchaseDetail_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchaseDetail_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblPurchaseDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPurchaseDetail_Update
(
	@Id int   ,

	@Code varchar (16)  ,

	@PurchaseId int   ,

	@ProductId int   ,

	@Quantity float   ,

	@Price int   
)
AS
BEGIN TRAN
	-- Get current row
	DECLARE @OldProductId INT
	DECLARE @OldQuantity INT

	SELECT @OldProductId = [ProductId], @OldQuantity = [Quantity]
	FROM tblPurchaseDetail
	WHERE [Id] = @Id

	-- Update PurchaseDetail
	UPDATE
		[dbo].[tblPurchaseDetail]
	SET
		[Code] = @Code
		,[PurchaseId] = @PurchaseId
		,[ProductId] = @ProductId
		,[Quantity] = @Quantity
		,[Price] = @Price
	WHERE [Id] = @Id

	IF(@@ERROR<>0)
		GOTO HANDLEERR

	-- Update Product
	DECLARE @RepositoryId INT
	SELECT @RepositoryId = [RepositoryId]
	FROM tblPurchase
	WHERE [Id] = @PurchaseId

	UPDATE tblStoredProduct
	SET [CurrentQuantity] = [CurrentQuantity] + @OldQuantity
	WHERE [RepositoryId] = @RepositoryId AND [ProductId] = @OldProductId

	IF(@@ERROR<>0)
		GOTO HANDLEERR

	UPDATE tblStoredProduct
	SET [CurrentQuantity] = [CurrentQuantity] - @Quantity
	WHERE [RepositoryId] = @RepositoryId AND [ProductId] = @ProductId

	IF(@@ERROR<>0)
		GOTO HANDLEERR

COMMIT TRAN
RETURN
HANDLEERR:
	ROLLBACK TRAN
GO

-- Drop the dbo.tblPurchaseDetail_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchaseDetail_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchaseDetail_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblPurchaseDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPurchaseDetail_Delete
(

	@Id int   
)
AS
BEGIN TRAN
	-- Get current PurchaseDetail
	DECLARE @Quantity INT
	DECLARE @ProductId INT
	DECLARE @PurchaseId	INT

	SELECT @ProductId = [ProductId], @Quantity = [Quantity], @PurchaseId = [PurchaseId]
	FROM tblPurchaseDetail
	WHERE [Id] = @Id

	-- Delete PurchaseDetail
	DELETE FROM [dbo].[tblPurchaseDetail] WITH (ROWLOCK) 
	WHERE
		[Id] = @Id

	IF(@@ERROR<>0)
		GOTO HANDLEERR

	-- Update Product
	DECLARE @RepositoryId INT
	SELECT @RepositoryId = [RepositoryId]
	FROM tblPurchase
	WHERE [Id] = @PurchaseId

	UPDATE tblStoredProduct
	SET [CurrentQuantity] = [CurrentQuantity] + @Quantity
	WHERE [RepositoryId] = @RepositoryId AND [ProductId] = @ProductId

	IF(@@ERROR<>0)
		GOTO HANDLEERR

COMMIT TRAN
RETURN
HANDLEERR:
	ROLLBACK TRAN
GO

SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO